import time,re
from _ssl import SSLError
from aifc import Error
import pymysql,requests
import pymysql.cursors
import traceback,urllib3
from bs4 import BeautifulSoup
from httpcore import ProxyError

urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
class Shops:
    #设置headers头
    def headers(self):
        header = {
            "Host": "xm.58.com",
            "Connection": "keep-alive",
            "Cache-Control": "max-age=0",
            "sec-ch-ua": '" Not;A Brand";v="99", "Google Chrome";v="91", "Chromium";v="91"',
            "sec-ch-ua-mobile": "?0",
            "Upgrade-Insecure-Requests": "1",
            "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.128 Safari/537.36",
            "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
            "Sec-Fetch-Site": "none",
            "Sec-Fetch-Mode": "navigate",
            "Sec-Fetch-User": "?1",
            "Sec-Fetch-Dest": "document",
            "Accept-Encoding": "gzip, deflate, br",
            "Accept-Language": "zh-CN,zh;q=0.9",
        }
        return header
    # 链接mysql数据库
    def connection(self, database_key):
        mysql = {'host': '', 'port': 3306, 'user': 'root', 'passwd': '', 'db': '', 'charset': 'utf8'}
        if database_key == 'soucheng_system':
            mysql['host'] = '47.99.62.97'
            mysql['passwd'] = 'QWERqwer@1234'
            # mysql['host'] = '127.0.0.1'
            # mysql['passwd'] = '123456'
            # mysql['host'] = '10.10.18.160'
            # mysql['passwd'] = 'root'
            mysql['db'] = 'soucheng_system'
        return mysql
    # 执行新增方法
    def insertSql(self, sql):
        bink = Shops()
        # 创建连接
        conn = pymysql.connect(**bink.connection('soucheng_system'))
        # 创建游标
        cursor = conn.cursor()
        # 执行SQL，并返回收影响行数
        cursor.execute(sql)
        # 提交执行
        conn.commit()
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
    #获取需要采集的URL 集合
    def findAll(self,sql):
        bink = Shops()
        db = pymysql.connect(**bink.connection('soucheng_system'))
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        result = cursor.fetchall();
        db.commit()
        cursor.close()
        db.close()
        return result
    # http GET 方法
    def getRequest(self,url):
        headers=self.headers()
        # 代理服务器
        proxyHost = "http-dyn.abuyun.com"
        proxyPort = "9020"
        # 代理隧道验证信息
        proxyUser = "HG0AX861PTEC0V7D"
        proxyPass = "50572CFD121D4640"
        proxyMeta = "http://%(user)s:%(pass)s@%(host)s:%(port)s" % {
            "host": proxyHost,
            "port": proxyPort,
            "user": proxyUser,
            "pass": proxyPass,
        }
        proxies = {
            "http": proxyMeta,
            "https": proxyMeta,
        }
        while True:
            try:
                response = requests.get(url=url, headers=headers, proxies=proxies, stream=True, verify=False,timeout=300)
                time.sleep(2)
                if(response.status_code==200):
                    return response
                else:
                    continue
            except SSLError:
                continue
            except ProxyError:
                continue
            except Error:
                continue
    #执行采集方法
    def select_form(self,sql):
        try:
            # 执行SQL语句 获取所有记录列表
            results = self.findAll(sql);
            for row in results:
                try:
                    city=row[0]
                    area=row[1]
                    range=row[2]
                    cz_url=row[3]
                    cs_url=row[4]
                    orgCode=row[5]
                    #============================出租
                    html=self.getRequest(cz_url).text
                    bsObj= BeautifulSoup(html,'lxml')
                    lis=bsObj.find("ul",{"id":"house-list-wrap"}).findAll("li");
                    # 先判断是否是今天的
                    for li in lis:
                        nn=li.find("div",{"class":"time"}).text
                        if '今天' in str(nn) or '小时前' in str(nn) or '分钟前' in str(nn) or '1天前' in str(nn):
                            # 获取详情链接 再判断是否已存在
                            try:
                                cuurl=li.find("a")["href"]
                                czText=self.getRequest(cuurl).text
                                czObj= BeautifulSoup(czText,'lxml')
                                isgeren=czObj.find("p",{"class":"poster-identity"})
                                if isgeren==None:
                                    continue
                                if "个人" in isgeren.text:
                                    houseId=r'.*?houseId=(.*?)&.*?'
                                    houseId=re.findall(houseId,str(cuurl),re.S)[0]
                                    spans=czObj.find("p",{"class":"house-update-info"}).findAll("span")
                                    jianjie=""
                                    liulannum=0
                                    if len(spans)>0:
                                        for sp in spans:
                                            if "人已浏览" in sp.text:
                                                liulannum=int(str(sp.text).replace("人已浏览",""));
                                            jianjie=jianjie+" "+sp.text
                                    #查询帖子是否存在
                                    if(houseId!=None):
                                        findSql="select count(0) from sc_system_shop where house_id='"+str(houseId)+"'"
                                        dataRe=self.findAll(findSql);
                                        if(dataRe[0][0]>= 1):
                                            updatesql="update sc_system_shop set jianjie='"+str(jianjie)+"',`number`= "+str(liulannum)+" where house_id='"+str(houseId)+"'"
                                            print(updatesql)
                                            self.insertSql(updatesql)
                                            continue
                                    title=czObj.find("div",{"class":"house-title"}).find("h1")
                                    if title!=None:
                                        title=str(title.text).replace(",","").replace("'","").replace("/n","").replace("/r","").replace("/t","").strip()
                                    else:
                                        title=""
                                    type="商铺出租"
                                    if "转让" in title:
                                        type="商铺转让"
                                    user=czObj.find("span",{"class":"name-text"})
                                    if user!=None:
                                        user=str(user.text).replace(",","").replace("'","").replace("/n","").replace("/r","").replace("/t","").strip()
                                    else:
                                        user=""
                                    address=czObj.find("span",{"class":"address"})
                                    if address!=None:
                                        address=str(address.text).replace(",","").replace("'","").replace("/n","").replace("/r","").replace("/t","").strip()
                                    else:
                                        address=""
                                    imageurl=li.find("img")["data-src"]
                                    yuezujin=str(li.find("p",{"class":"up"}).text).replace(",","").replace("'","").replace("\n","").replace("\r","").replace("\t","").strip()
                                    rizujin=str(li.find("p",{"class":"down"}).text).replace(",","").replace("'","").replace("\n","").replace("\r","").replace("\t","").strip()
                                    areanum=str(li.find("p",{"class":"num"}).findAll("span")[0].text).replace(",","").replace("'","").replace("\n","").replace("\r","").replace("\t","").strip()
                                    areaunit=str(li.find("p",{"class":"num"}).findAll("span")[1].text).replace(",","").replace("'","").replace("\n","").replace("\r","").replace("\t","").strip()
                                    shop_statuss = str(li.select('div.list-info.intro_sty_2 > p:nth-child(2) > span:nth-child(5)')[0].text).replace(",","").replace("'","").replace("\n","").replace("\r","").replace("\t","").strip()
                                    if("空置中" in shop_statuss):
                                        shop_statuss= -1
                                    else:
                                        shop_statuss=0
                                    hangye=r'<span class="title">经营类型</span>.*?class="content">(.*?)</span>'
                                    hangye=re.findall(hangye,czText,re.S)[0]
                                    detailinfo=""
                                    detailinfos=czObj.find("div",{"class":"general-item-wrap"}).findAll("div",{"class":"des-item"})
                                    if len(detailinfos)>0:
                                        for di in detailinfos:
                                            detailinfo=detailinfo+"\n"+str(di.text).replace(",","").replace("'","").replace("\n","").replace("\r","").replace("\t","").strip()
                                    # 用户头像
                                    userinfo=czObj.find("a",{"class":"poster-img"})
                                    userImg=userinfo.find("img")["src"]
                                    userUrl=str(userinfo["href"]).replace("?","")
                                    uid=r'.*?/person/(.*?)personType.*?'
                                    uid=re.findall(uid,str(userUrl),re.S)[0]
                                    orCode=czObj.find("div",{"class":"mnp-qr"}).find("img")["src"]
                                    zrprice=czObj.find("span",{"class":"house_basic_title_money_num_second"})
                                    if zrprice != None:
                                        zrprice=str(zrprice.text).replace(",","").replace("'","").replace("/n","").replace("/r","").replace("/t","").strip()
                                    else:
                                        zrprice=''
                                    sqls='INSERT INTO `soucheng_system`.`sc_system_shop`(`house_id`,`title`,`image_url`,`uid`,`user_img`,`uname`,`address`,`city`,`area`,' \
                                         '`ranges`,`cuurl`,`yuezujin`,`rizujin`,`area_num`,`area_unit`,`shop_statuss`,`hangye`,`jianjie`,`detail_info`,`or_code`,`data_type`,`create_time`,`sys_org_code`,`number`,`zrprice`) ' \
                                         ' VALUES ("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")'% \
                                         ( houseId,title,imageurl,uid,userImg,user,address,city,area,range,cuurl,yuezujin,rizujin,
                                           areanum,areaunit,shop_statuss,hangye,jianjie,detailinfo,orCode,type,time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),orgCode,liulannum,zrprice)
                                    # print(sqls)
                                    self.insertSql(sqls)
                            except Exception as e:
                                continue
                except Exception as e:
                    continue

                # 再判断是否是个人的
        except Exception as e:
            print(traceback.print_exc())
        finally:
            pass
    def main(self):
        sql="SELECT c3.name cname,c2.name carea,c.name careas,c.cz_url,c.cs_url,c.sys_org_code  FROM `sc_system_city`c  left join sc_system_city c2 on c2.id=c.pid  left join sc_system_city c3 on c3.id=c2.pid  where c.level=3 and c.pid >=3059 and c.pid<=3065 "
        while True:
            self.select_form(sql)
            time.sleep(200)

if __name__ == '__main__':
    binke=Shops()
    binke.main()
